Extending prices and subtotaling the extensions
In an orders report or invoice, you may need to extend the prices for individual line items and then subtotal the extensions. You do this by using a simple formula to extend the prices, and then you subtotal the formula field.
To extend the price and subtotal the extensions
- Create a report using the sample data, Xtreme.mdb, and place the following fields from left to right in the Details section:
{orders.CUSTOMER ID}
{orders detail.PRODUCT ID}
{orders detail.QUANTITY}
{orders detail.UNIT PRICE}
- To create the formula for extending the prices, choose Insert from the main menu and select Field Object.
The Field Explorer dialog box appears.
Select Formula Fields and click New.
The Formula Name dialog box appears.
- Enter the name you want to identify the formula.
- Click OK.
The Formula Editor appears.
- Enter the following formula into the Formula text box:
{Orders_Detail.Quantity} * {orders detail.Unit Price}
Click the Save and Close button when finished.
You return to the Field Explorer dialog box with the name of your formula listed in the Formula Fields directory.
- Double-click your formula and place the formula field to the right of the Unit Price field in the Details section of the report.
- Click Close to close the Field Explorer dialog box.
- To subtotal your extension formula, right-click the formula field, point to Insert and choose Subtotal from the shortcut menu.
The Insert Subtotal dialog box appears.
- To create a subtotal for weekly sales, select {orders.ORDER DATE} as the sort-and-group-by field, and choose for each week as the group interval.
Note: The "section will be printed" drop-down box will not be activated until you choose the Order Date field.
- Click OK to insert the subtotal in the report.
Your data will be sorted by date and grouped in one week intervals. Your final report should look similar to the following: